/*
 * PKL 51 GUNUNG KIDUL
 * Subseksie DATA ENTRY + CAPI
 */
package pkl51.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import pkl51.database.DatabaseUtility;
import pkl51.entity.Blok5;
import pkl51.service.Blok5Dao;
import pkl51.error.EntityException;

/**
 *
 * @author ERI
 */
public class Blok5DaoImpl implements Blok5Dao {

    private Connection connection;
    private final String insertBlok5 =
            "INSERT INTO Blok5 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE B5R1a = ? , B5R1b = ?, B5R1c = ?, B5R2ia = ?, B5R2ib = ?, B5R2ic = ?, B5R2id = ?, B5R2iia = ?, B5R2iib = ?, B5R2iic = ?, B5R2iid = ?, B5R2iie = ?, B5R2iif = ?, B5R2iig = ?, B5R2iih = ?, B5R2iiia = ?, B5R2iiib = ?, B5R2iiic = ?";
    private final String updateBlok5 =
            "UPDATE Blok5 SET B5R1a = ? , B5R1b = ?, B5R1c = ?, B5R2ia = ?, B5R2ib = ?, B5R2ic = ?, B5R2id = ?, B5R2iia = ?, B5R2iib = ?, B5R2iic = ?, B5R2iid = ?, B5R2iie = ?, B5R2iif = ?, B5R2iig = ?, B5R2iih = ?, B5R2iiia = ?, B5R2iiib = ?, B5R2iiic = ? WHERE NKS = ?";
    private final String getBlok5ByNks =
            "SELECT * FROM Blok5 WHERE NKS = ?";

    public void insertBlok5(Blok5 blok5) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(insertBlok5); //isikan statement yang akan dieksekusi

            statement.setString(1, blok5.getNKS());
            statement.setString(2, blok5.getB5R1a());
            statement.setString(3, blok5.getB5R1b());
            statement.setString(4, blok5.getB5R1c());
            try {
                statement.setInt(5, blok5.getB5R2ia());
            } catch (Exception e) {
                statement.setNull(5, Types.INTEGER);
            }
            try {
                statement.setInt(6, blok5.getB5R2ib());
            } catch (Exception e) {
                statement.setNull(6, Types.INTEGER);
            }
            try {
                statement.setInt(7, blok5.getB5R2ic());
            } catch (Exception e) {
                statement.setNull(7, Types.INTEGER);
            }
            try {
                statement.setInt(8, blok5.getB5R2id());
            } catch (Exception e) {
                statement.setNull(8, Types.INTEGER);
            }
            try {
                statement.setInt(9, blok5.getB5R2iia());
            } catch (Exception e) {
                statement.setNull(9, Types.INTEGER);
            }
            try {
                statement.setInt(10, blok5.getB5R2iib());
            } catch (Exception e) {
                statement.setNull(10, Types.INTEGER);
            }
            try {
                statement.setInt(11, blok5.getB5R2iic());
            } catch (Exception e) {
                statement.setNull(11, Types.INTEGER);
            }
            try {
                statement.setInt(12, blok5.getB5R2iid());
            } catch (Exception e) {
                statement.setNull(12, Types.INTEGER);
            }
            try {
                statement.setInt(13, blok5.getB5R2iie());
            } catch (Exception e) {
                statement.setNull(13, Types.INTEGER);
            }
            try {
                statement.setInt(14, blok5.getB5R2iif());
            } catch (Exception e) {
                statement.setNull(14, Types.INTEGER);
            }
            try {
                statement.setInt(15, blok5.getB5R2iig());
            } catch (Exception e) {
                statement.setNull(15, Types.INTEGER);
            }
            try {
                statement.setInt(16, blok5.getB5R2iih());
            } catch (Exception e) {
                statement.setNull(16, Types.INTEGER);
            }
            try {
                statement.setInt(17, blok5.getB5R2iiia());
            } catch (Exception e) {
                statement.setNull(17, Types.INTEGER);
            }
            try {
                statement.setInt(18, blok5.getB5R2iiib());
            } catch (Exception e) {
                statement.setNull(18, Types.INTEGER);
            }
            try {
                statement.setInt(19, blok5.getB5R2iiic());
            } catch (Exception e) {
                statement.setNull(19, Types.INTEGER);
            }
            statement.setString(20, blok5.getB5R1a());
            statement.setString(21, blok5.getB5R1b());
            statement.setString(22, blok5.getB5R1c());
            try {
                statement.setInt(23, blok5.getB5R2ia());
            } catch (Exception e) {
                statement.setNull(23, Types.INTEGER);
            }
            try {
                statement.setInt(24, blok5.getB5R2ib());
            } catch (Exception e) {
                statement.setNull(24, Types.INTEGER);
            }
            try {
                statement.setInt(25, blok5.getB5R2ic());
            } catch (Exception e) {
                statement.setNull(25, Types.INTEGER);
            }
            try {
                statement.setInt(26, blok5.getB5R2id());
            } catch (Exception e) {
                statement.setNull(26, Types.INTEGER);
            }
            try {
                statement.setInt(27, blok5.getB5R2iia());
            } catch (Exception e) {
                statement.setNull(27, Types.INTEGER);
            }
            try {
                statement.setInt(28, blok5.getB5R2iib());
            } catch (Exception e) {
                statement.setNull(28, Types.INTEGER);
            }
            try {
                statement.setInt(29, blok5.getB5R2iic());
            } catch (Exception e) {
                statement.setNull(29, Types.INTEGER);
            }
            try {
                statement.setInt(30, blok5.getB5R2iid());
            } catch (Exception e) {
                statement.setNull(30, Types.INTEGER);
            }
            try {
                statement.setInt(31, blok5.getB5R2iie());
            } catch (Exception e) {
                statement.setNull(31, Types.INTEGER);
            }
            try {
                statement.setInt(32, blok5.getB5R2iif());
            } catch (Exception e) {
                statement.setNull(32, Types.INTEGER);
            }
            try {
                statement.setInt(33, blok5.getB5R2iig());
            } catch (Exception e) {
                statement.setNull(33, Types.INTEGER);
            }
            try {
                statement.setInt(34, blok5.getB5R2iih());
            } catch (Exception e) {
                statement.setNull(34, Types.INTEGER);
            }
            try {
                statement.setInt(35, blok5.getB5R2iiia());
            } catch (Exception e) {
                statement.setNull(35, Types.INTEGER);
            }
            try {
                statement.setInt(36, blok5.getB5R2iiib());
            } catch (Exception e) {
                statement.setNull(36, Types.INTEGER);
            }
            try {
                statement.setInt(37, blok5.getB5R2iiic());
            } catch (Exception e) {
                statement.setNull(37, Types.INTEGER);
            }

            statement.executeUpdate();
            connection.commit();
        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                System.out.println(ex1.getMessage());
            }
            System.out.println(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    System.out.println(ex.getMessage());
                }
            }
        }
    }

    public void updateBlok5(Blok5 blok5) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(updateBlok5); //isikan statement yang akan dieksekusi

            statement.setString(1, blok5.getB5R1a());
            statement.setString(2, blok5.getB5R1b());
            statement.setString(3, blok5.getB5R1c());
            try {
                statement.setInt(4, blok5.getB5R2ia());
            } catch (Exception e) {
                statement.setNull(4, Types.INTEGER);
            }
            try {
                statement.setInt(5, blok5.getB5R2ib());
            } catch (Exception e) {
                statement.setNull(5, Types.INTEGER);
            }
            try {
                statement.setInt(6, blok5.getB5R2ic());
            } catch (Exception e) {
                statement.setNull(6, Types.INTEGER);
            }
            try {
                statement.setInt(7, blok5.getB5R2id());
            } catch (Exception e) {
                statement.setNull(7, Types.INTEGER);
            }
            try {
                statement.setInt(8, blok5.getB5R2iia());
            } catch (Exception e) {
                statement.setNull(8, Types.INTEGER);
            }
            try {
                statement.setInt(9, blok5.getB5R2iib());
            } catch (Exception e) {
                statement.setNull(9, Types.INTEGER);
            }
            try {
                statement.setInt(10, blok5.getB5R2iic());
            } catch (Exception e) {
                statement.setNull(10, Types.INTEGER);
            }
            try {
                statement.setInt(11, blok5.getB5R2iid());
            } catch (Exception e) {
                statement.setNull(11, Types.INTEGER);
            }
            try {
                statement.setInt(12, blok5.getB5R2iie());
            } catch (Exception e) {
                statement.setNull(12, Types.INTEGER);
            }
            try {
                statement.setInt(13, blok5.getB5R2iif());
            } catch (Exception e) {
                statement.setNull(13, Types.INTEGER);
            }
            try {
                statement.setInt(14, blok5.getB5R2iig());
            } catch (Exception e) {
                statement.setNull(14, Types.INTEGER);
            }
            try {
                statement.setInt(15, blok5.getB5R2iih());
            } catch (Exception e) {
                statement.setNull(15, Types.INTEGER);
            }
            try {
                statement.setInt(16, blok5.getB5R2iiia());
            } catch (Exception e) {
                statement.setNull(16, Types.INTEGER);
            }
            try {
                statement.setInt(17, blok5.getB5R2iiib());
            } catch (Exception e) {
                statement.setNull(17, Types.INTEGER);
            }
            try {
                statement.setInt(18, blok5.getB5R2iiic());
            } catch (Exception e) {
                statement.setNull(18, Types.INTEGER);
            }
            statement.setString(19, blok5.getNKS());

            statement.executeUpdate();
            connection.commit();
        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                System.out.println(ex1.getMessage());
            }
            System.out.println(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    System.out.println(ex.getMessage());
                }
            }
        }
    }

    public Blok5 getBlok5ByNks(String NKS) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(getBlok5ByNks); //isikan statement yang akan dieksekusi

            statement.setString(1, NKS);

            ResultSet resultSet = statement.executeQuery();

            Blok5 blok5 = null;
            if (resultSet.next()) {
                blok5 = new Blok5();
                blok5.setNKS(resultSet.getString("NKS"));
                blok5.setB5R1a(resultSet.getString("B5R1a"));
                blok5.setB5R1b(resultSet.getString("B5R1b"));
                blok5.setB5R1c(resultSet.getString("B5R1c"));
                try {
                    blok5.setB5R2ia(resultSet.getInt("B5R2ia"));
                } catch (Exception e) {
                    blok5.setB5R2ia(null);
                }
                try {
                    blok5.setB5R2ib(resultSet.getInt("B5R2ib"));
                } catch (Exception e) {
                    blok5.setB5R2ib(null);
                }
                try {
                    blok5.setB5R2ic(resultSet.getInt("B5R2ic"));
                } catch (Exception e) {
                    blok5.setB5R2ic(null);
                }
                try {
                    blok5.setB5R2id(resultSet.getInt("B5R2id"));
                } catch (Exception e) {
                    blok5.setB5R2id(null);
                }
                try {
                    blok5.setB5R2iia(resultSet.getInt("B5R2iia"));
                } catch (Exception e) {
                    blok5.setB5R2iia(null);
                }
                try {
                    blok5.setB5R2iib(resultSet.getInt("B5R2iib"));
                } catch (Exception e) {
                    blok5.setB5R2iib(null);
                }
                try {
                    blok5.setB5R2iic(resultSet.getInt("B5R2iic"));
                } catch (Exception e) {
                    blok5.setB5R2iic(null);
                }
                try {
                    blok5.setB5R2iid(resultSet.getInt("B5R2iid"));
                } catch (Exception e) {
                    blok5.setB5R2iid(null);
                }
                try {
                    blok5.setB5R2iie(resultSet.getInt("B5R2iie"));
                } catch (Exception e) {
                    blok5.setB5R2iie(null);
                }
                try {
                    blok5.setB5R2iif(resultSet.getInt("B5R2iif"));
                } catch (Exception e) {
                    blok5.setB5R2iif(null);
                }
                try {
                    blok5.setB5R2iig(resultSet.getInt("B5R2iig"));
                } catch (Exception e) {
                    blok5.setB5R2iig(null);
                }
                try {
                    blok5.setB5R2iih(resultSet.getInt("B5R2iih"));
                } catch (Exception e) {
                    blok5.setB5R2iih(null);
                }
                try {
                    blok5.setB5R2iiia(resultSet.getInt("B5R2iiia"));
                } catch (Exception e) {
                    blok5.setB5R2iiia(null);
                }
                try {
                    blok5.setB5R2iiib(resultSet.getInt("B5R2iiib"));
                } catch (Exception e) {
                    blok5.setB5R2iiib(null);
                }
                try {
                    blok5.setB5R2iiic(resultSet.getInt("B5R2iiic"));
                } catch (Exception e) {
                    blok5.setB5R2iiic(null);
                }

            } else {
                throw new SQLException("field tidak ditemukan");
            }

            connection.commit();

            return blok5;

        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                System.out.println(ex1.getMessage());
            }
            System.out.println(ex.getMessage());
            throw new EntityException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    System.out.println(ex.getMessage());
                }
            }
        }
    }
}
